﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MicropackReporterGenerator
{
    public partial class CreateReport : Form
    {
        string _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        string _fileName = "";
        
        public CreateReport()
        {
            InitializeComponent();
        }

        private void CreateReport_Load(object sender, EventArgs e)
        {
            hojasComboBox.DataSource = GetSheets();
        }

        private void generarReporteButton_Click(object sender, EventArgs e)
        {
            List<Objects.Product> products = new List<Objects.Product>();
            string _consulta = "select * from {0} where GRUPO_DESC is not null order by FLIA_DESC, SUB_FLIA_DESC";

            //Toma la Hoja de TextBox.
            _consulta = string.Format(_consulta, "[" + hojasComboBox.Text + "$]");

            string connection = string.Format(_connectionString, _fileName);

            using (OleDbConnection con = new OleDbConnection(connection))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand(_consulta, con);
                try
                {
                    using (OleDbDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Objects.Product p = new Objects.Product();

                            p.CLAS_LTA = (string)dr["CLAS_LTA"];
                            p.COMBINACION = (string)dr["COMBINACION"];
                            p.FLIA_DESC = (string)dr["FLIA_DESC"];
                            p.PRECIO = (decimal)dr["PRECIO"];
                            p.PRES = (string)dr["PRES"];
                            p.SUB_FLIA_DESC = dr["SUB_FLIA_DESC"] == DBNull.Value ? "" : (string)dr["SUB_FLIA_DESC"];

                            products.Add(p);
                        }
                    }
                }
                catch (Exception)
                {
                }

                ReportShower rs = new ReportShower(GetDS(products));
                rs.ShowDialog();

                con.Close();
            }
        }

        private string GetDS(List<Objects.Product> Productos)
        {
            string final = string.Empty;
            string _Cat = "<h3>{0}</h3> <br/>";
            string _SubCat = "<h4>{0}</h4> <br/>";
            string _Det = "{0} {1} {2} {3} <br/>";

            for (int i = 0; i < Productos.Count; i++)
            {
                if (i == 0)
                    final = InicialWhen0(Productos, final, _Cat, _SubCat, _Det, i);
                else
                {
                    if (Productos[i].FLIA_DESC != Productos[i - 1].FLIA_DESC) //Cambio la Categoria                    
                        final += string.Format(_Cat, Productos[i].FLIA_DESC);
                    if (Productos[i].SUB_FLIA_DESC != Productos[i - 1].SUB_FLIA_DESC) // Cambio la subcategoria                    
                        final += string.Format(_SubCat, Productos[i].SUB_FLIA_DESC);

                    final += string.Format(_Det, Productos[i].COMBINACION, Productos[i].PRES, Productos[i].CLAS_LTA, Productos[i].PRES);
                }
            }
            return final;
        }

        private static string InicialWhen0(List<Objects.Product> Productos, string final, string _Cat, string _SubCat, string _Det, int i)
        {
            final += string.Format(_Cat, Productos[i].FLIA_DESC);
            final += string.Format(_SubCat, Productos[i].SUB_FLIA_DESC);
            foreach (var item in Productos.Where(x => x.FLIA_DESC == Productos[i].FLIA_DESC && x.SUB_FLIA_DESC == Productos[i].SUB_FLIA_DESC))
                final += string.Format(_Det, item.COMBINACION, item.PRES, item.CLAS_LTA, item.PRES);
            return final;
        }


        private List<CustomExcelSheet> GetSheets()
        {
            List<CustomExcelSheet> MyList = new List<CustomExcelSheet>();


            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() != DialogResult.OK) return MyList;

            _fileName = dialog.FileName;

            string connectionstr = string.Format(_connectionString, dialog.FileName);

            OleDbConnection connection = new OleDbConnection(connectionstr);
            connection.Open();
            var Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < Sheets.Rows.Count; i++)
                if (!Sheets.Rows[i].ItemArray[2].ToString().Contains("FilterDatabase"))
                    MyList.Add(new CustomExcelSheet(i, Sheets.Rows[i]));

            return MyList;
        }

        public class CustomExcelSheet
        {
            public int Index { get; private set; }
            public string Name { get; private set; }

            public CustomExcelSheet(int _Index, DataRow dr)
            {
                Index = _Index;
                Name = dr.ItemArray[2].ToString().Replace("$", "");
            }
        }
    }
}
